<?php

class m_document extends CI_Model{
	
	function _list($data){
		extract($data);
		IF ($_SESSION[SESSION_GROUPTIPE] == 1){
			$sql = " SELECT DISTINCT
					concat (document_noflow, document_noproyek, document_no) as document_noall,
					document_no,
					document_noproyek,
					document_noflow,
					t_document.t_project_id,
					project_name,project_id,project_value,
					project_code,
					t_document.m_flow_id,
					document_id,
					posisi,
					ifnull(m_div.div_name,
                    if (posisi is not null, 'Selesai','')) div_name,
					m_div.div_id,
					flowdet2.flowdet_id,flowdet3.s_users_id user1,flowdet2.s_users_id user2, 
					if (flowdet3.s_users_id <> flowdet2.s_users_id, 1, 0) as nextuser,
					max_tracking_seq,
					tracking2.tracking_flag
				FROM  m_flow JOIN t_project LEFT JOIN t_document
						ON t_document.t_project_id = project_id AND m_flow_id = flow_id
			/*	JOIN m_flow ON t_document.m_flow_id = flow_id */
				JOIN m_flowdet as flowdet1 ON flowdet1.m_flow_id = flow_id
				LEFT JOIN (select 
						t_document_id,
						( SUM(if (tracking_flag = 5, 1, 0)  )
							- SUM(if (tracking_flag = 6, 1, 0) ) 
						 + 1 ) posisi,
							 max(tracking_seq) max_tracking_seq
					from t_tracking
					group by t_tracking.t_document_id  ) trackdoc on trackdoc.t_document_id = document_id
				LEFT JOIN m_flowdet as flowdet2 
					ON flowdet2.m_flow_id = flow_id AND flowdet2.flowdet_seq = posisi
				LEFT JOIN m_flowdet as flowdet3 
					ON flowdet3.m_flow_id = flow_id AND flowdet3.flowdet_seq = (posisi - 1)
				LEFT JOIN t_userproject 
					ON t_userproject.t_project_id = t_project.project_id 
				LEFT JOIN m_div 
					ON flowdet2.m_div_id = div_id
				LEFT JOIN t_tracking as tracking2
					ON tracking2.tracking_seq = max_tracking_seq
						and tracking2.t_document_id = document_id
				WHERE 1= 1 ";
            if (strtolower($_SESSION[SESSION_GROUPNAMA]) == "cabang" )
                 $sql .=    "  AND m_cabang_id = ".$_SESSION[SESSION_CABANGID]." ";
             else if (strtolower($_SESSION[SESSION_GROUPNAMA]) == "proyek" )
                 $sql .=    "  AND t_userproject.s_users_id = '".$_SESSION[SESSION_USERID]."' ";
         
            /*    $sql .=    "   /* flowdet1.m_div_id = '".$_SESSION[SESSION_DIV]."'
				AND flowdet1.s_users_id = '".$_SESSION[SESSION_USERID]."'
				AND m_div.div_id is not null * / "; */
            
		}else{
			$sql = "SELECT 
						concat (document_noflow, document_noproyek, document_no) as document_noall,
						document_no,	
					document_noproyek,
					document_noflow,
						t_project_id,
						project_name,project_code,project_id,project_value,
						t_document.m_flow_id,
						document_id,
						posisi,
						m_div.div_name,
						m_div.div_id,
						flowdet2.flowdet_id,
						if (flowdet3.s_users_id <> flowdet2.s_users_id, 1, 0) as nextuser,
						max_tracking_seq,
						tracking2.tracking_flag
					FROM m_flow JOIN t_project LEFT JOIN t_document
						ON t_project_id = project_id AND m_flow_id = flow_id
					LEFT JOIN (select 
							t_document_id,
							( SUM(if (tracking_flag = 5, 1, 0)  )
								- SUM(if (tracking_flag = 6, 1, 0) ) 
							 + 1 ) posisi,
							 max(tracking_seq) max_tracking_seq
						from t_tracking
						group by t_tracking.t_document_id  ) trackdoc on trackdoc.t_document_id = document_id
				/*	RIGHT JOIN m_flow ON t_document.m_flow_id = flow_id */
					LEFT JOIN m_flowdet as flowdet2 
						ON flowdet2.m_flow_id = flow_id AND flowdet2.flowdet_seq = posisi
					LEFT JOIN m_flowdet as flowdet3 
						ON flowdet3.m_flow_id = flow_id AND flowdet3.flowdet_seq = (posisi - 1)
					LEFT JOIN m_div 
						ON flowdet2.m_div_id = div_id
					LEFT JOIN t_tracking as tracking2
						ON tracking2.tracking_seq = max_tracking_seq
							and tracking2.t_document_id = document_id
					WHERE   1 = 1
			";
		}
		if (@$document_noall != "") $sql .= " AND document_noall like '%".@$document_noall."%'";
		if (@$project_name != "") $sql .= " AND project_name like '%".@$project_name."%'";
		if (@$project_code != "") $sql .= " AND project_code like '%".@$project_code."%'";
		if (@$m_flow_id != "") $sql .= " AND ( m_flow.flow_id = '".@$m_flow_id."' ) ";
		
		$query = $this->db->query($sql);
		$records = $query->num_rows();
		
		$sql = "SELECT * FROM (".$sql.") k ORDER BY posisi LIMIT ".(($page - 1) * $rows).",".$rows;
		$query = $this->db->query($sql);
		$result = $query->result();
		
		return '{ 
		  "total": "'.ceil($records / $rows).'", 
		  "page": "'.$page.'", 
		  "records": "'.$records .'",
		  "rows" : '.json_encode($result).'
		}';
	}
	
	function _insert($data){
		$this->db->insert('t_document',$data);
		$document_id = $this->db->insert_id();
		
		$data_tracking = array(
			"tracking_seq" => 1,
			"t_document_id" => $document_id,
			"tracking_flag" => 5,
			"tracking_date" => date("Y-m-d h:i:s")
		);
		$this->db->insert('t_tracking',$data_tracking);
		
		return $document_id;
	}
	
	function _update($data,$id){
		$this->db->where('document_id',$id);
		$this->db->update('t_document',$data);
		return 1;
	}
	
	function _delete($data){
		$this->db->delete('t_document',$data);
		return 1;
	}
	
	function _process($data, $checklist, $where){
		if( $where == false){ 
			$this->db->insert('t_tracking',$data);
			$t_tracking_id = $this->db->insert_id();
			
		}else{
			$this->db->update('t_tracking',$data,$where);
			
			$this->db->where($where);
			$query = $this->db->get('t_tracking');
			$tracking_id =  $query->row("tracking_id");
			if (count($tracking_id) > 0){
				$this->db->where("t_tracking_id",$tracking_id);
				$this->db->delete('t_trackingdet');
				$t_tracking_id = $tracking_id;
			}
		}
        if (count($checklist) > 0){
            foreach(@$checklist as $m_flowdetchecklist_id=>$trackingdet_flag){
                $data_checklist = array(
                    "m_flowdetchecklist_id" => $m_flowdetchecklist_id,
                    "trackingdet_flag" => $trackingdet_flag,
                    "t_tracking_id" => $t_tracking_id
                );
                $this->db->insert('t_trackingdet',$data_checklist);
            }
        }
		return 1;
	}
	
	function posflowbydiv($flow_id, $div_id){
		$sql = "SELECT flowdet_seq FROM m_flow  
				JOIN m_flowdet ON m_flowdet.m_flow_id = flow_id
				WHERE m_div_id = '".mysql_real_escape_string($div_id)."'
				AND flow_id = '".mysql_real_escape_string($flow_id)."'";
		$query  = $this->db->query($sql);
		$flowdet_seq =  $query->row("flowdet_seq");
		
		return  $flowdet_seq;
	}
	
	function poslastflow($flow_id){
		$sql = "SELECT m_div_id as max_flowdet_seq FROM m_flow  
				JOIN m_flowdet ON m_flowdet.m_flow_id = flow_id
				WHERE flow_id = '".mysql_real_escape_string($flow_id)."'
                ORDER BY flowdet_seq DESC";
		$query  = $this->db->query($sql);
		$max_flowdet_seq =  $query->row("max_flowdet_seq");
		
		return $max_flowdet_seq;
	}
	
	function is_update($t_document_id, $max_tracking_seq){
		$where = array(
			't_document_id' => $t_document_id,
			'tracking_seq' => $max_tracking_seq
		);
		$this->db->where($where);
		$query = $this->db->get('t_tracking');
		if ($query->num_rows() > 0){
			if ($query->row("tracking_flag") < 5)
				return true;
			else
				return false;
		}else
			return false;
	}
	
	function flowcodebyid($flow_id){
		$sql = "SELECT flow_code, flow_subcode FROM m_flow WHERE flow_id = '".mysql_real_escape_string($flow_id)."'";
		$query = $this->db->query($sql);
		$row = $query->row();
		return $row;
	}
}